<!DOCTYPE html>
<html>

<head>
    <meta charset="UTF-8"/>
    <title>SQLTable</title>

    <link rel='stylesheet' href='https://cdn.jsdelivr.net/npm/luckysheet/dist/plugins/css/pluginsCss.css'/>
    <link rel='stylesheet' href='https://cdn.jsdelivr.net/npm/luckysheet/dist/plugins/plugins.css'/>
    <link rel='stylesheet' href='https://cdn.jsdelivr.net/npm/luckysheet/dist/css/luckysheet.css'/>
    <link rel='stylesheet' href='https://cdn.jsdelivr.net/npm/luckysheet/dist/assets/iconfont/iconfont.css'/>
    <script src="https://cdn.jsdelivr.net/npm/luckysheet/dist/plugins/js/plugin.js"></script>
    <script src="https://cdn.jsdelivr.net/npm/luckysheet/dist/luckysheet.umd.js"></script>


    <script src="./luckysheet/js/axios-0.18.0.js"></script>
    <script src="./luckysheet/js/vue.js"></script>

    <!-- 引入样式 -->
    <link rel="stylesheet" href="./luckysheet/css/index.css">
    <!-- 引入组件库 -->
    <script src="./luckysheet/js/index.js"></script>
    <script src="./luckysheet/js/url.js"></script>

</head>
<body>

<div id="app">
    <div>
        {{tname}}
        <el-date-picker
                v-model="time"
                type="month"
                value-format="yyyy-MM"
                placeholder="选择期间">
        </el-date-picker>
        <el-select
                v-model="oid"
                multiple
                collapse-tags
                :clearable="true"
                style="margin-left: 20px;width: 380px"
                placeholder="请选择组织">
            <el-option
                    v-for="item in organizationList"
                    :key="item.name"
                    :label="item.name"
                    :value="item.name">
            </el-option>
        </el-select>
        <el-button type="primary" size="small" @click="select" round>查询</el-button>
        <el-button type="info" style="float: right" size="small" @click="call" round>返回</el-button>
        <el-button type="primary" style="float: right;margin-right: 10px" size="small" @click="drawer = true" round>条件
        </el-button>
    </div>
    <div style="width: 100%;height: 90vh;margin-top: 10px">
        <div id="luckysheet"
             style="margin:0px;padding:0px;width:100%;height: 100%"></div>
    </div>

    <el-drawer
            title="sql配置"
            size="40%"
            :wrapper-closable="false"
            :visible.sync="drawer">
        <el-form label-position="right" label-width="100px" style="margin-right: 50px">
            <el-row>
                <el-col :span="12">
                    <el-form-item label="数据库类型">
                        <el-select v-model="formData.driver" placeholder="请选择数据库类型" clearable>
                            <el-option label="Mysql" value="0"></el-option>
                            <el-option label="Oracle" value="1"></el-option>
                        </el-select>
                    </el-form-item>
                </el-col>
                <el-col :span="12">
                    <el-form-item label="连接地址">
                        <el-input v-model="formData.url"/>
                    </el-form-item>
                </el-col>
            </el-row>
            <el-row>
                <el-col :span="12">
                    <el-form-item label="用户名">
                        <el-input v-model="formData.username"/>
                    </el-form-item>
                </el-col>
                <el-col :span="12">
                    <el-form-item label="密码">
                        <el-input type="password" v-model="formData.password"/>
                    </el-form-item>
                </el-col>
            </el-row>
            <el-row v-for="item,index in formData.sqlshow">
                <el-divider content-position="left">{{index+1}}段SQL配置</el-divider>
                <!--                {{item}}-->
                <el-row>
                    <el-col :span="24">
                        <el-form-item label="sql">
                            <el-input type="textarea" v-model="item.sql"/>
                        </el-form-item>
                    </el-col>
                </el-row>
                <el-row>
                    <el-col :span="24">
                        <el-form-item label="匹配条件">
                            <template>
                                <el-table
                                        :data="item.tableData"
                                        height="250"
                                        border
                                        style="width: 100%">
                                    <el-table-column
                                            label="字段">
                                        <template slot-scope="scope">
                                            <el-input v-model="scope.row.field"/>
                                        </template>
                                    </el-table-column>
                                    <el-table-column
                                            label="映射">
                                        <template slot-scope="scope">
                                            <el-input v-model="scope.row.map"/>
                                        </template>
                                    </el-table-column>
                                    <el-table-column
                                            label="操作" width="80">
                                        <template slot-scope="scope">
                                            <el-button size="small" type="danger"
                                                       @click="deleteMapping(item.tableData,scope.row)"
                                                       round>
                                                删除
                                            </el-button>
                                        </template>
                                    </el-table-column>
                                </el-table>
                            </template>
                        </el-form-item>
                    </el-col>
                </el-row>
                <el-row>
                    <el-col :span="24">
                        <el-button style="float: right;margin-right: 10px" type="danger"
                                   @click="deleteConfiguration(index)">删除配置组
                        </el-button>
                        <el-button style="float: right;margin-right: 10px" type="primary"
                                   @click="addMapping(item.tableData)">添加映射
                        </el-button>
                    </el-col>
                </el-row>
            </el-row>
            <el-divider content-position="left">操作</el-divider>
            <el-row style="margin-bottom: 10px">
                <el-col :span="24">
                    <el-button style="float: right" type="success" @click="findSQL()">查询</el-button>
                    <el-button style="float: right;margin-right: 10px" type="primary" @click="output()">输出该类表
                    </el-button>
                    <el-button style="float: right;margin-right: 10px" type="info" @click="addConfiguration()">添加配置组
                    </el-button>
                </el-col>
            </el-row>
        </el-form>
    </el-drawer>

</div>

<script>
    var vue = new Vue({
        el: '#app',
        data: {
            options: {},
            token: {
                headers: {
                    'Authorization': "Bearer eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJhdWQiOlsicmVzMSJdLCJ1c2VyX25hbWUiOiJrdWlodWEwMDEiLCJzY29wZSI6WyJhbGwiXSwiZXhwIjoxNjM2NzIyMTkxLCJhdXRob3JpdGllcyI6WyJSLWhlYWRPZmZpY2UiLCJraDAwMSJdLCJqdGkiOiI2NjliODg0Zi0xNjE4LTQ5MjAtYjViZi1jODAwYjU3NjgxNGIiLCJjbGllbnRfaWQiOiJjMSJ9.iEiJ9gP6l7rDw3UiAj-eARmOXCzn-vaaphv3j7e9hH4"
                }
            },
            luckysheet: "",
            drawer: false,
            formData: {
                driver: "",
                url: "",
                username: "",
                password: "",
                sqlshow: [],
                sql: []
            },
            organizationList: [],
            tid: 286,
            oid: "",
            oname: "",
            time: "",
            tname: "测试",
            backendUrl: "http://127.0.0.1:18082",
        },
        created() {
            this.backendUrl = window.backendUrl
            this.luckysheet = Object.assign({}, luckysheet);
            Request = this.GetRequest();
            this.token = {
                headers: {
                    'Authorization': Request.token
                }
            }
            this.tid = Request.tid;
            this.tname = Request.tname;

            axios.get(this.backendUrl + "/organization", this.token).then((res) => {

                this.organizationList = res.data.data

            }).catch((err) => {
                console.log(err);
            });
            this.findSqlCreated()

            this.$notify.info({
                title: '提示',
                dangerouslyUseHTMLString: true,
                message: '<span>' + "点击" + "<span style='color:blue'>" + "条件" + "</span>" + "进行sql配置" + '</span>',
                position: 'bottom-left',
            });
        },
        mounted: function () {

        },
        methods: {

            findSqlCreated() {
                axios.get(this.backendUrl + "/template/findByPid/" + this.tid, this.token).then((res) => {
                    let data = res.data.data[0]
                    if (data.rowId == -1) {
                        data = res.data.data[0].data.slice(1, -1)
                        configuration = data.replace(/~/g, "'")
                        configuration = JSON.parse(configuration)
                        this.formData = configuration
                    }
                }).catch((err) => {
                    console.log(err);
                });
            },

            //参数初始化
            optionsS() {
                this.options = {
                    container: 'luckysheet',
                    title: 'Luckysheet Demo',
                    lang: 'zh', // 设定表格语言
                    row: 36, //行数
                    column: 18, //列数
                    // 是否显示工具栏
                    showtoolbar: false,
                    // 是否显示顶部信息栏
                    showinfobar: false,
                    // 是否显示sheet页签
                    showsheetbar: false,
                    // 是否显示公式栏
                    sheetFormulaBar: false,
                    // 是否允许新增行
                    enableAddRow: false,
                    // 是否允许返回顶部
                    enableAddBackTop: false,
                    hook: {
                        // cellUpdated: "",
                        // cellUpdateBefore: ""
                    },
                    data: [{
                        // sheet名
                        name: "sheet1",
                        defaultColWidth: 150, //自定义列宽
                        index: "0",
                        config: {
                            // 单元格合并配置
                            "merge": {},
                            // 单元格边框配置
                            "borderInfo": [{
                                "rangeType": "range",
                                "borderType": "border-all",
                                "style": "1",
                                "color": "#000000",
                                "range": [{
                                    "row": [7, 8],
                                    "column": [2, 3]
                                }]
                            }],
                        },
                        // 单元格数据配置
                        celldata: [],
                        calcChain: []
                    }],
                }
            },

            //  删除映射信息
            deleteMapping(tableData, row) {
                for (var i = 0; i < tableData.length; i++) {
                    if (tableData[i].key == row.key) {
                        tableData.splice(i, 1);
                    }
                }
            },

            GetRequest() {
                var url = location.search; //获取url中"?"符后的字串

                var theRequest = new Object();
                if (url.indexOf("?") != -1) {
                    var str = url.substr(1);
                    strs = str.split("&");
                    for (var i = 0; i < strs.length; i++) {
                        theRequest[strs[i].split("=")[0]] = decodeURI(strs[i].split("=")[1]);
                    }
                }
                return theRequest;
            },
            // 获取表内数据
            getAll() {
                var list = luckysheet.getluckysheetfile()[0].data
            },
            // 查询源数据
            findSourceBody(lastDataList) {

                this.optionsS()

                luckysheet = this.luckysheet

                let sourceList = lastDataList

                // 设置表格大小
                this.options.column = sourceList[0].length;
                this.options.row = sourceList.length;

                // console.log(sourceList)

                // 设置边框范围
                this.options.data[0].config["borderInfo"][0]["range"][0]["row"] = [0, sourceList.length - 1]
                this.options.data[0].config["borderInfo"][0]["range"][0]["column"] = [0, sourceList[0].length - 1]

                for (var i = 0; i < sourceList.length; i++) {
                    for (var j = 0; j < sourceList[i].length; j++) {
                        var item = {}
                        var gs = {}

                        if ((sourceList[i][j] + "").substring(0, 1) == "=") {

                            item = {
                                "r": i,
                                "c": j,
                                "v": {

                                    "ct": {"fa": 'General', "t": 'g'},
                                    "f": sourceList[i][j].split("&")[0].replace(/\$/g, '"'),
                                    "ht": 1,
                                    "m": sourceList[i][j].split("&")[1],
                                    "v": sourceList[i][j].split("&")[1],
                                },
                            }

                            gs = {
                                "r": i,
                                "c": j,
                                "index": "0",
                                "func": [
                                    true,
                                    sourceList[i][j].split("&")[1],
                                    sourceList[i][j].split("&")[0].replace(/\$/g, '"')
                                ],
                                "color": "w",
                                "parent": null,
                                "chidren": {},
                                "times": 0
                            }

                            this.options.data[0].calcChain.push(gs)
                        } else {
                            item = {
                                "r": i,
                                "c": j,
                                "v": {
                                    "ct": {
                                        "fa": "General",
                                        "t": "n"
                                    },
                                    "v": sourceList[i][j],
                                    "m": sourceList[i][j],
                                    "vt": 0,
                                    "ht": 1
                                },
                            }
                        }

                        this.options.data[0].celldata.push(item)
                    }
                }

                luckysheet.create(this.options);

            },

            //    保存变更后数据
            save() {
                //    获取模板数据
                data_list = luckysheet.getSheetData(0)

                for (const row_index in data_list) {
                    for (const col_index in data_list[row_index]) {
                        if (data_list[row_index][col_index] != null && data_list[row_index][col_index].hasOwnProperty('f')) {
                            data_list[row_index][col_index] = data_list[row_index][col_index].f + "&" + data_list[row_index][col_index].v

                            data_list[row_index][col_index] = data_list[row_index][col_index].replace(/"/g, '$');
                        } else if (data_list[row_index][col_index] != null && data_list[row_index][col_index].hasOwnProperty('v')) {
                            data_list[row_index][col_index] = data_list[row_index][col_index].v
                        } else {
                            data_list[row_index][col_index] = ""
                        }
                    }
                }

                // 组装更改对象
                for (const row_index in data_list) {
                    data_list[row_index] = {
                        "pid": this.tid,
                        "data": JSON.stringify(data_list[row_index]),
                        "rowId": row_index
                    };
                }

                axios.post(this.backendUrl + "/template/alterTemplate", data_list, this.token).then((res) => {

                    this.$message({
                        message: res.data.message,
                        type: res.data.flag ? "success" : "error"
                    });

                }).catch((err) => {
                    console.log(err);
                });
            },

            openFullScreen2() {
                const loading = this.$loading({
                    lock: true,
                    text: 'Loading',
                    spinner: 'el-icon-loading',
                    background: 'rgba(0, 0, 0, 0.7)'
                });
                return loading;
            },

            //sql查询
            findSQL() {

                this.formData.sql = []

                // 判断公式是否为空
                if (this.oid.length == 0) {
                    for (let index in this.organizationList) {
                        this.oid.push(this.organizationList[index]["name"])
                    }
                }

                let gs = ""

                for (let index in this.oid) {
                    gs = gs + "'" + this.oid[index] + "'" + ","
                }

                gs = gs.slice(0, -1)

                this.oname = gs

                // 时间变量${time}  单位变量${unit}

                let sqlshow = this.formData.sqlshow
                for (let i = 0; i < sqlshow.length; i++) {
                    this.formData.sql.push(this.parameterReplace(sqlshow[i].sql, ["{time}", "{oname}"]))
                }


                //开启遮罩层
                var loading = this.openFullScreen2()

                axios.post(this.backendUrl + "/jdbc", this.formData, this.token).then((res) => {

                    var resDataList = res.data.data

                    let lastDataList = []
                    for (let resDataIndex in resDataList) {
                        let resData = resDataList[resDataIndex]
                        let dataList = []
                        let tableData = this.formData.sqlshow[resDataIndex].tableData
                        // 装配表头
                        for (let index in tableData) {
                            dataList.push(tableData[index].map);
                        }

                        lastDataList.push(dataList)

                        for (let dataIndex in resData) {
                            //sql头映射
                            let headers = tableData
                            let data = []
                            for (let headerIndex in headers) {
                                let field = headers[headerIndex].field
                                data.push(resData[dataIndex][field])
                            }
                            lastDataList.push(data)
                        }
                        lastDataList.push([])
                    }

                    // 渲染表数据
                    this.findSourceBody(lastDataList);

                }).catch((err) => {

                    this.$notify.error({
                        title: '查询失败',
                        dangerouslyUseHTMLString: true,
                        message: '<span>' + this.formData.sql + '</span>',
                        position: 'top-left',
                        duration: 0
                    });

                    console.log(err);
                }).finally(() => {
                    loading.close();
                })
            },

            // 参数替换方法
            parameterReplace(sql, paramList) {
                //遍历参数列表
                for (const paramListKey in paramList) {
                    paramCrude = paramList[paramListKey]
                    //获取参数名
                    param = paramCrude.slice(1, -1)
                    while (true) {
                        if (sql.indexOf(paramCrude) == -1) {
                            break
                        } else {
                            sql = sql.replace(paramCrude, this[param])
                        }
                    }
                }
                return sql;
            },

            select() {
                this.findSQL()
            },

            //添加映射条件
            addMapping(tableData) {
                tableData.push({key: Math.random()})
            },

            //添加配置组
            addConfiguration() {
                try {
                    this.formData.sqlshow.push({sql: "", tableData: []})
                } catch (errnoError) {
                    // 配置不规范
                    this.formData = {
                        driver: "",
                        url: "",
                        username: "",
                        password: "",
                        sqlshow: [],
                        sql: []
                    },
                        this.formData.sqlshow.push({sql: "", tableData: []})
                }

            },

            //删除配置组
            deleteConfiguration(index) {
                this.formData.sqlshow.splice(index, 1);
            },

            // 输出该类表
            output() {

                this.$confirm('此操作将覆盖模板样式, 是否继续?', '提示', {
                    confirmButtonText: '确定',
                    cancelButtonText: '取消',
                    type: 'warning'
                }).then(() => {

                    this.formData.sql = []

                    data_list = [[]]
                    configuration = JSON.stringify(this.formData).replace(/'/g, "~")
                    data_list[0] = {
                        "pid": this.tid,
                        "data": JSON.stringify(configuration),
                        "rowId": -1
                    };


                    axios.post(this.backendUrl + "/template/alterTemplate", data_list, this.token).then((res) => {

                        this.$message({
                            message: res.data.message,
                            type: res.data.flag ? "success" : "error"
                        });

                    }).catch((err) => {
                        this.$message({
                            message: "输出sql模板失败",
                            type: "error"
                        });
                        console.log(err);
                    });

                }).catch((err) => {
                    alert(err)
                    this.$message({
                        type: 'info',
                        message: '已取消输出'
                    });
                    return
                });
            },

            call() {
                window.open("call=binding.template_bind.bind=back")
            },
        }
    })
</script>
</body>
</html>